package test0701;

//1：设计一个DbUtil类，处理相关数据库的操作，最好用Static方法
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbUtil {
	static String url = "jdbc:mysql://127.0.0.1:3306/myschool?useSSL=false";
	static String username = "root";
	static String password = "zy940425";
	static Connection connection = null;
	static PreparedStatement pstmt = null;
	static ResultSet rs = null;
	{
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("未找到驱动！");
			e.printStackTrace();
		}

	}

	// 修改URL
	public static void changeURL(String newURL) {
		url = newURL;
	}

	// 修改账号密码
	public static void changeURL(String newUsername, String newPassword) {
		username = newUsername;
		password = newPassword;
	}

	// 关闭所有方法
	public static void close() {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (pstmt != null)
				pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (connection != null)
				connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 增
	public static void add(String table, String[] key, String[] value) {
		try {
			connection = DriverManager.getConnection(url, username, password);
			String strkey = "";
			String strvalue = "";
			for (int i = 0; i < key.length; i++) {
				if (i < key.length - 1) {
					strkey += key[i] + ",";
					strvalue += "?,";
				} else {
					strkey += key[i];
					strvalue += "?";
				}
			}
			String sql = "insert into " + table + "(" + strkey + ") values(" + strvalue + ")";
			pstmt = connection.prepareStatement(sql);
			// pstmt.setString(1, table);
			for (int i = 0; i < key.length; i++) {
				pstmt.setString(i + 1, value[i]);
			}
			System.out.println(sql);
			System.out.println(pstmt.executeUpdate() + "条增加成功！");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

	}

	// 删
	public static void delete(String table, String key, String value) {
		try {
			connection = DriverManager.getConnection(url, username, password);
			String sql = "delete from " + table + " where " + key + "=?;";
			pstmt = connection.prepareStatement(sql);
			// pstmt.setString(1, table);
			// pstmt.setString(1, key);
			pstmt.setString(1, value);
			// System.out.println(sql);
			System.out.println(pstmt.executeUpdate() + "条删除成功！");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
	}

	// 查
	public static void select(String table, String... key) {
		try {
			connection = DriverManager.getConnection(url, username, password);
			String strkey = "";
			for (int i = 0; i < key.length; i++) {
				if (i < key.length - 1) {
					strkey += key[i] + ",";
				} else {
					strkey += key[i];
				}
			}
			// System.out.println(strkey);
//			for (int i = 0; i < key.length; i++) {
//				if (i < key.length - 1) {
//					strkey += "?,";
//				} else {
//					strkey += "?";
//				}
//			}
			String sql = "select " + strkey + " from " + table;
			pstmt = connection.prepareStatement(sql);
//			for (int i = 0; i < key.length; i++) {
//				pstmt.setString(i + 1, key[i]);
//			}
			// pstmt.setString(key.length, table);
			// System.out.println(sql);
			rs = pstmt.executeQuery();
			for (int i = 0; i < key.length; i++) {
				if (i == key.length - 2) {
					System.out.print(key[i] + "\t\t");
				} else {
					System.out.print(key[i] + "\t");
				}

			}
			System.out.println();
			while (rs.next()) {
				for (int i = 0; i < key.length; i++) {
					System.out.print(rs.getString(key[i]) + "\t");

				}
				System.out.println();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
	}

	// 改
	public static void updata(String table, String key, String oldValue, String newValue) {
		try {
			connection = DriverManager.getConnection(url, username, password);
			String sql = "update " + table + " set " + key + "=? where " + key + "=?;";
			pstmt = connection.prepareStatement(sql);
			// pstmt.setString(1, table);
			// pstmt.setString(1, key);
			pstmt.setString(1, newValue);
			// pstmt.setString(3, key);
			pstmt.setString(2, oldValue);
			System.out.println(sql);
			System.out.println(pstmt.executeUpdate() + "条更改成功！");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
	}

	public static void main(String[] args) {
		// DbUtil.add("test0703", new String[] { "name", "addr" }, new String[] {
		// "王小二","合肥包河" });
		// DbUtil.delete("test0703", "name", "王二麻23");
		DbUtil.select("test0703", "id", "name", "addr", "CREATEDATA", "t_date");
		// DbUtil.updata("test0703", "addr", "凤阳", "淮北");

	}

}
